/*******************************************************************************
"SCARCITY WITHOUT LEVIATHAN: The Violent Effects of Cocaine Supply Shortages
in the Mexican Drug War"
J.C. Castillo, D. Mejia, P. Restrepo
*********************************************************************************

This do file produces:  
TABLE 1: "Summary Statistics"

The table presents summary statistics for the main variables used in the empirical
excersises.

Before runnning: set the path to current folder at line 21
*******************************************************************************/

clear all
set matsize 5000
set more off

*Set local folder
local folder /*SET THE PATH TO CURRENT FOLDER HERE*/

*Set working directory
cd `folder'
use dta\CastilloMejiaRestrepo.dta

keep if year>=2006

gen tasaINEGI = (homSIMBAD/poblacion) * 100000 * 12 //  Monthly homicide rate per 100,000 inhabitants from INEGI
gen tasaDR  = (tothomicideB/poblacion) * 100000 * 12 // Drug-related homicide rate in Mexico

//Table 1 -  First Panel - Homicide rate (annualized)
*************************************************************
file open myfilea using Tables\Table1pan1a.tex, write replace
file open myfileb using Tables\Table1pan1b.tex, write replace

file write myfilea "Homicide rate (annualized)   & "
file write myfileb "                       & "

*2006
sum tasaINEGI [w=poblacion] if year==2006
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum tasaINEGI [w=poblacion] if year==2007
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum tasaINEGI [w=poblacion] if year==2008
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum tasaINEGI [w=poblacion] if year==2009
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum tasaINEGI [w=poblacion] if year==2010
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  Second Panel - Homicide rate (annualized) - Quint 1 & 2
******************************************************************
file open myfilea using Tables/Table1pan2a.tex, write replace
file open myfileb using Tables/Table1pan2b.tex, write replace

file write myfilea "Homicide rate (annualized)   & "
file write myfileb " & "

*2006
sum tasaINEGI [w=poblacion] if year==2006&q12==1 
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum tasaINEGI [w=poblacion] if year==2007&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum tasaINEGI [w=poblacion] if year==2008&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum tasaINEGI [w=poblacion] if year==2009&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum tasaINEGI [w=poblacion] if year==2010&q12==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 


//Table 1 -  Third Panel - Homicide rate (annualized) - Municip. close to US border
**********************************************************************************
file open myfilea using Tables/Table1pan3a.tex, write replace
file open myfileb using Tables/Table1pan3b.tex, write replace

file write myfilea "Homicide rate (annualized)   & "
file write myfileb "  & "

*2006
sum tasaINEGI [w=poblacion] if year==2006&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum tasaINEGI [w=poblacion] if year==2007&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum tasaINEGI [w=poblacion] if year==2008&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum tasaINEGI [w=poblacion] if year==2009&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum tasaINEGI [w=poblacion] if year==2010&q1==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  First Panel - Drug-related homicide rate
*************************************************************
file open myfilea using Tables/Table1pan4a.tex, write replace
file open myfileb using Tables/Table1pan4b.tex, write replace

file write myfilea "Drug-related homicide rate    & "
file write myfileb "                       & "

*2006
sum tasaDR [w=poblacion] if year==2006
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum tasaDR [w=poblacion] if year==2007
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum tasaDR [w=poblacion] if year==2008
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum tasaDR [w=poblacion] if year==2009
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum tasaDR [w=poblacion] if year==2010
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 


//Table 1 -  Second Panel - Drug-related homicide rate - Quint. 1 & 2
**********************************************************************
file open myfilea using Tables/Table1pan5a.tex, write replace
file open myfileb using Tables/Table1pan5b.tex, write replace

file write myfilea "Drug-related homicide rate    & "
file write myfileb " & "

*2006
sum tasaDR [w=poblacion] if year==2006&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum tasaDR [w=poblacion] if year==2007&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum tasaDR [w=poblacion] if year==2008&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum tasaDR [w=poblacion] if year==2009&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum tasaDR [w=poblacion] if year==2010&q12==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 


//Table 1 -  Third Panel - Drug-related homicide rate - Municip. close to US border
********************************************************************************
file open myfilea using Tables/Table1pan6a.tex, write replace
file open myfileb using Tables/Table1pan6b.tex, write replace

file write myfilea "Drug-related homicide rate    & "
file write myfileb "  & "

*2006
sum tasaDR [w=poblacion] if year==2006&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum tasaDR [w=poblacion] if year==2007&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum tasaDR [w=poblacion] if year==2008&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum tasaDR [w=poblacion] if year==2009&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum tasaDR [w=poblacion] if year==2010&q1==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  First Panel - Municip. w/ cartels, share
********************************************************************************
file open myfilea using Tables/Table1pan7a.tex, write replace
file open myfileb using Tables/Table1pan7b.tex, write replace

file write myfilea "Share of municipalities with cartels    & "
file write myfileb "                       & "

*2006
sum hayCartel if year==2006
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum hayCartel if year==2007
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum hayCartel if year==2008
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum hayCartel if year==2009
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum hayCartel if year==2010
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 


//Table 1 -  Second Panel - Municip. w/ cartels, share - Quint. 1 & 2
********************************************************************************
file open myfilea using Tables/Table1pan8a.tex, write replace
file open myfileb using Tables/Table1pan8b.tex, write replace

file write myfilea "Share of municipalities with cartels    & "
file write myfileb " & "

*2006
sum hayCartel if year==2006&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum hayCartel if year==2007&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum hayCartel if year==2008&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum hayCartel if year==2009&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum hayCartel if year==2010&q12==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 


//Table 1 -  Third Panel - Municip. w/ cartels, share - Municip. close to US Border
********************************************************************************
file open myfilea using Tables/Table1pan9a.tex, write replace
file open myfileb using Tables/Table1pan9b.tex, write replace

file write myfilea "Share of municipalities with cartels    & "
file write myfileb "  & "

*2006
sum hayCartel if year==2006&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum hayCartel if year==2007&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum hayCartel if year==2008&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum hayCartel if year==2009&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum hayCartel if year==2010&q1==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  First Panel - Population (in thousands) 
********************************************************************************
gen pop1000=poblacion/1000

file open myfilea using Tables/Table1pan10a.tex, write replace
file open myfileb using Tables/Table1pan10b.tex, write replace

file write myfilea "Population (in thousands)  & "
file write myfileb "                       & "

*2006
sum pop1000 if year==2006
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum pop1000 if year==2007
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum pop1000 if year==2008
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum pop1000 if year==2009
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum pop1000 if year==2010
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  Second Panel - Population (in thousands) - Quint. 1 & 2
********************************************************************************
file open myfilea using Tables/Table1pan11a.tex, write replace
file open myfileb using Tables/Table1pan11b.tex, write replace

file write myfilea "Population (in thousands)    & "
file write myfileb " & "

*2006
sum pop1000 if year==2006&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum pop1000 if year==2007&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum pop1000 if year==2008&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum pop1000 if year==2009&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum pop1000 if year==2010&q12==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  Third Panel - Population (in thousands) - Munic. close to US Border
********************************************************************************
file open myfilea using Tables/Table1pan12a.tex, write replace
file open myfileb using Tables/Table1pan12b.tex, write replace

file write myfilea "Population (in thousands)    & "
file write myfileb "  & "

*2006
sum pop1000 if year==2006&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum pop1000 if year==2007&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum pop1000 if year==2008&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum pop1000 if year==2009&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum pop1000 if year==2010&q1==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  First Panel - PAN vote share
********************************************************************************
file open myfilea using Tables/Table1pan13a.tex, write replace
file open myfileb using Tables/Table1pan13b.tex, write replace

file write myfilea "Historical PAN vote share (time invariant)  & "
file write myfileb "                       & "

*2006
sum PANsupport [w=poblacion] if year==2006
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum PANsupport [w=poblacion] if year==2007
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum PANsupport [w=poblacion] if year==2008
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum PANsupport [w=poblacion] if year==2009
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum PANsupport [w=poblacion] if year==2010
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 


//Table 1 -  Second Panel - PAN vote share - Quant. 1 & 2
********************************************************************************
file open myfilea using Tables/Table1pan14a.tex, write replace
file open myfileb using Tables/Table1pan14b.tex, write replace

file write myfilea "Historical PAN vote share (time invariant)  & "
file write myfileb " & "

*2006
sum PANsupport [w=poblacion] if year==2006&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum PANsupport [w=poblacion] if year==2007&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum PANsupport [w=poblacion] if year==2008&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum PANsupport [w=poblacion] if year==2009&q12==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum PANsupport [w=poblacion] if year==2010&q12==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 

//Table 1 -  Third Panel - PAN vote share - Munic. close to US Border
********************************************************************************
file open myfilea using Tables/Table1pan15a.tex, write replace
file open myfileb using Tables/Table1pan15b.tex, write replace

file write myfilea "Historical PAN vote share (time invariant) & "
file write myfileb "  & "

*2006
sum PANsupport [w=poblacion] if year==2006&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2007
sum PANsupport [w=poblacion] if year==2007&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2008
sum PANsupport [w=poblacion] if year==2008&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2009
sum PANsupport [w=poblacion] if year==2009&q1==1
file write myfilea %7.2f (r(mean)) " & "
file write myfileb "[" %7.2f (r(sd)) "]" " & "

*2010
sum PANsupport [w=poblacion] if year==2010&q1==1
file write myfilea %7.2f (r(mean)) " \\ "
file write myfileb "[" %7.2f (r(sd)) "]" " \\ "

file close myfilea 
file close myfileb 
